package com.sl.au.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Bid;

public interface BidRepository extends JpaRepository<Bid, String> {
	// 获得所有的投标记录
	@Query(" select b from Bid b where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' ) and b.auditState = ?2")
	Page<Bid> findBid(String key,String auditState, Pageable pageable);

	@Query(" select count(*) from Bid b  where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' ) and b.auditState = ?2")
	Long getCount(String key,String auditState);
	
	//未审批审批
	@Query(" select b from Bid b where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' ) and (b.auditState <> '通过' and b.auditState <> '驳回')")
	Page<Bid> findBid(String key, Pageable pageable);
	@Query(" select b from Bid b where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' )")
	Page<Bid> findBidAll(String key, Pageable pageable);

	@Query(" select count(*) from Bid b  where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' ) and (b.auditState <> '通过' and b.auditState <> '驳回')")
	Long getCount(String key);
	@Query(" select count(*) from Bid b  where ( b.customerId like '%'+?1+'%' or b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%'" 
			+ "or b.ownerUnit like '%'+?1+'%' ) ")
	Long getCountAll(String key);

	/**
	 * 得到本部门数据
	 * 
	 * @param key
	 *            查询条件
	 * @param superior
	 *            部门
	 * @return page
	 */
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState = ?4")
	Page<Bid> findBid(String key, String superior, String userId, String auditState ,Pageable pageable);

	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState = ?4")
	Long getCount(String key, String superior,String userId, String auditState);
	
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState <> '通过' and b.auditState <> '驳回'")
	Page<Bid> findBid(String key, String superior, String userId, Pageable pageable);
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过')))")
	Page<Bid> findBidAll(String key, String superior, String userId, Pageable pageable);

	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState <> '通过' and b.auditState <> '驳回'")
	Long getCount(String key, String superior, String userId);
	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and (b.user.saleArea.superior =?2) and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) ")
	Long getCountAll(String key, String superior, String userId);

	/**
	 * 得到本片区数据
	 * 
	 * @param key
	 *            查询条件
	 * @param superior
	 *            部门
	 * @return page
	 */
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState = ?4")
	Page<Bid> findAreaBid(String key, String areaId, String UserId, String auditState , Pageable pageable);

	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState = ?4")
	Long getAreaCount(String key, String areaId, String auditState , String userId);
	
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState <> '通过' and b.auditState <> '驳回'")
	Page<Bid> findAreaBid(String key, String areaId, String UserId, Pageable pageable);
	@Query(" select b from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过')))")
	Page<Bid> findAreaBidAll(String key, String areaId, String UserId, Pageable pageable);

	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) and b.auditState <> '通过' and b.auditState <> '驳回'")
	Long getAreaCount(String key, String areaId, String userId);
	@Query(" select count(*) from Bid b where ((b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.saleArea.id =?2 and "
			+ "(b.user.id =?3 or (b.auditState='审批中' or b.auditState='已提交' or b.auditState='通过'))) ")
	Long getAreaCountAll(String key, String areaId, String userId);

	/**
	 * 得到本人的数据
	 * 
	 * @param key
	 *            查询条件
	 * @param superior
	 *            部门
	 * @return page
	 */
	@Query(" select b from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2 and b.auditState = ?3")
	Page<Bid> findSelfBid(String key, String userId, String auditState , Pageable pageable);

	@Query(" select count(*) from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2 and b.auditState = ?3")
	Long getSelfCount(String key, String areaId, String auditState );
	
	
	@Query(" select b from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2 and b.auditState <> '通过' and b.auditState <> '驳回'")
	Page<Bid> findSelfBid(String key, String userId, Pageable pageable);
	@Query(" select b from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2")
	Page<Bid> findSelfBidAll(String key, String userId, Pageable pageable);

	@Query(" select count(*) from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2 and b.auditState <> '通过' and b.auditState <> '驳回'")
	Long getSelfCount(String key, String areaId);
	@Query(" select count(*) from Bid b where (b.name like '%'+?1+'%' "
			+ "or b.projectType like '%'+?1+'%' or b.launch like '%'+?1+'%' "
			+ "or b.launcher like '%'+?1+'%' or b.bidUnit like '%'+?1+'%' "
			+ "or b.ownerUnit like '%'+?1+'%' or b.auditState like '%'+?1+'%') and b.user.id =?2 ")
	Long getSelfCountAll(String key, String areaId);

	// 得到一个投标
	Bid findById(String id);

	// 删除
	@Transactional
	@Modifying
	@Query("delete from CustomerVisitPerson k where k.customerVisiting.id=?1 ")
	public void deletePerson(String visitId);

}
